#!/usr/bin/env python
# Copyright(c) 2010 Nuttachat Wisittipanit. All rights reserved.

from mahbo.information.database.utility.dbinterface import DatabaseInterface
from mahbo.utility.utility import (DataPrePackage,)
from minings import Datap
from minings.utility import utility
import orange,numpy,MySQLdb,cPickle


class RDPLevel(DatabaseInterface):
    
    def __init__(self,**kwargs):
        self.maindbname       = '' #'gutmtps'
        self.level              = '' #RDP LEVEL 1 -> 1
        self.rdpDbName          = '' #'rdp8level1'
        self.rdpDbNameM         = '' #'rdp8level1m'
        self.maindata           = None
        self.__dict__.update(kwargs)
        DatabaseInterface.__init__(self,self.maindbname,True)
        #[Dys100_19,Dys100_37,..]
        self.sampleName         = []    
        #[0,1,2,1] Disease in index format
        self.nameIndex          = []    
        #{'Dys100_19':Ulcerative Colitis, ..}
        self.name2class         = {}    
        #{"Crohns":0,"Crohns-ileitis":1,"Diverticulitis":2,
        # "Polyp" :3,"Pouchitis":4     ,"Ulcerative colitis":5,"Healthy Control":6}
        self.class2index        = {}    
        #["Crohns","Crohns-ileitis","Diverticulitis","Polyp",
        #"Pouchitis","Ulcerative colitis","Healthy Control"]
        self.classList          = []
        self.preSample          = []    
        self.preSampleD         = []
        self.nameIndexD         = []
        #[[0,0.106,..],[0,0.055,..],..]                            
        self.sample             = None  
        #Matrix of data
        self.sample_d           = None  
#        #Feature Lookup -> [('A1','Alpha Bacteria'),..]
        #info table keeps the data like this
        #'rdplevel1_ileum',info_obj
        self.rawdata            = {}
#        self.flookup            = [] 
        #['Acidobacteria','Actinobacteria',..]
        self.featuresName       = []    
        #list of features used for MySQL
        self.featuresNameMySQL  = [] 
        #[orange.FloatVariable('Acidobacteria'),orange.FloatVariable('Actinobacteria'),..]  
        self.features           = []    
        #Features used for MySQL
        self.featuresMySQL      = []
        self.metaSampleSite     = []
        self.metaSex            = []
        self.metaAge            = []
        self.metaMethod         = []
        self.metaClassX         = []
        self.metaClass1         = []
        self.metaClass2         = []
        self.metaClass3         = []
        self.orngData           = None  #orange.ExampleTable object
        self.nameColP           = [] #List of (0,Dys100_19),.. which does not have fingerId in COC file
        self.nameForb           = [] #List of (12,Dys101_50).. which LHPCR does not have
        #{'Acidobacteria Acidobacteria Acidobacteriales Acidobacteriaceae Gp2':'A_1',..}
        self.feature2index      = {}
        #{'A_1':'Acidobacteria Acidobacteria Acidobacteriales Acidobacteriaceae Gp2',..}
        self.index2feature      = {}
        self.rdpData            = None #RDPData instance
        #For Run
        self.maindataoption     = Datap.Option.fix
        self.mainpermremovelist = ['Polyp','Diverticulitis','Normal Pouch','Pouchitis','Alcoholic','ALD','Non ALD']
        self.keeplist           = ['Crohn','Ulcerative Colitis','Healthy Control']
        self.mainfixedcls       = 'Healthy Control'
        self.mainrun            = utility.RUN()
    
    def processArray(self):
        self.modifyFeatures()
        for i in range(len(self.classList)):
            self.class2index[self.classList[i]] = i
        for i in self.sampleName:
            #i               -> 'Dys100_19'
            #self.sampleName -> ['Dys100_19','Dys100_37,..],self.nameIndex -> [1,0,..] (indexes of disease) 
            self.nameIndex.append(self.class2index[self.name2class[i]])
        self.preSampleD     = numpy.array(self.preSample,'d')
        self.nameIndexD     = numpy.array(self.nameIndex,'d')
    
    def toOrangeTable(self):
        self.processArray()
        #Vertical stacking of data and class indexes
        self.sample         = numpy.vstack((self.preSampleD,self.nameIndex))
        self.sample_d       = self.sample.T #Transpose
        newClassVar         = orange.EnumVariable('Disease',values = self.classList)
        finalDomain         = orange.Domain(self.featuresMySQL,newClassVar)
        data                = orange.ExampleTable(finalDomain,self.sample_d)
        sampleName_id       = orange.newmetaid()
        classX_id           = orange.newmetaid()
        class1_id           = orange.newmetaid()
        data.domain.addmeta(sampleName_id,orange.StringVariable('SampleName'))
        data.domain.addmeta(classX_id,orange.StringVariable('ClassX'))
        data.domain.addmeta(class1_id,orange.StringVariable('Class1'))
        for i in range(len(data)):
            data[i]['SampleName']   = self.sampleName[i]
            data[i]['ClassX']       = self.metaClassX[i]
            data[i]['Class1']       = self.metaClass1[i]
        self.maindata       = utility.DatasetTools.finalBrush(data)
        ###########
#        self.maindata.save('%s.tab' % self.rdpDbName)
    
    def modifyFeatures(self):
        gname       = 'A'
        templist    = []
        uselist     = []
        cnt         = 0
        for fname in self.featuresName:
            xname       = fname
#            xname       = fname.replace('.','_')
#            xname       = xname.replace('=','')
#            xname       = xname.replace('[','')
#            xname       = xname.replace('__','')
#            xname       = xname.replace('+','')
#            xname       = xname.replace('-','')
            xname       = xname.replace('"','')
#            xname       = xname.replace(':','')
            dname       = '%s_%s' % (gname,cnt)
            templist.append((xname,dname))
            uselist.append(dname)
            cnt     += 1
        for orgnname,usename in templist: #@UnusedVariable
            self.featuresNameMySQL.append(usename)
            self.featuresMySQL.append(orange.FloatVariable(usename))
            self.index2feature[usename]     = orgnname
            self.feature2index[orgnname]    = usename

    def getRawdataMySQL(self):
        #Find all table names begining with 'rdp8level1_'
        reg             = '^%s_' % self.rdpDbName
        self.RD.query("""SELECT * FROM %s WHERE %s REGEXP "%s" 
                      """ % (self.RD.tab_info.tablename,self.RD.tab_info.name,reg) )
        self.doAll_bool = False
        tempraw         = []
        #In this case, it will be like [{'m$Sample_Site':'1'},{'m$Sample_Site':'2'}, .. ]
        row             = self.RD.cursor.fetchall() 
        if row not in (None,()):
            #Check whether I have everything ready to construct a datapackage
            for i in row:
                #dataname -> 'rdplevel1_ileum'
                dataname    = i[self.RD.tab_info.name]
                info        = cPickle.loads(i[self.RD.tab_info.info_obj])
                tempraw.append((dataname,info))
                try:
                    self.RD.query("""DESC %s""" % (dataname))
                except (MySQLdb.ProgrammingError,):
                    #If atleast one dataname does not exist, trigger doAll
                    self.doAll_bool = True
                    break
        else:
            self.doAll_bool = True
        #Other thing is that if i have 
        if self.doAll_bool:
            self.doAll()
        else:
            for i in tempraw:
                tempdata            = self.t.query("""SELECT * FROM %s""" % i[0])
                tempdata            = utility.DatasetTools.finalBrush(tempdata)
                self.rawdata[i[0]]  = GutMTPSDataPackage(fullname       = i[0]                  ,
                                                         data           = tempdata              ,
                                                         dataoption     = i[1].dataoption       ,
                                                         permremovelist = i[1].permremovelist   ,
                                                         keeplist       = i[1].keeplist         ,
                                                         fixedcls       = i[1].fixedcls         ,
                                                         matrix         = i[1].matrix           ,
                                                         pair           = i[1].pair             ,
                                                         run            = i[1].run              ,
                                                         Class_X        = i[1].Class_X          ,
                                                         Class_1        = i[1].Class_1          ,
                                                         Length         = i[1].Length           ,) 
    
    def doAll(self):
        ##########################
        ###Start Gathering Data###
        ##########################
        self.rawdata           = {} #{'gut_1':DataPackage instance, .. }
        #['Entire_Colon2','Endo_Ileum',..] list of dataset name
        self.keyRank           = []
        self.getAlldata()
    
    def getAlldata(self):
        #Get 'Entire','Ileum','Colon','Sigmoid','Lumen'
        xname                  = 'Entire'
        d_entire               = self.t.query("""SELECT * FROM %s WHERE ( (%s in ("%s","%s","%s","%s")) and (%s!="%s") ) 
                                                 """ % (self.rdpDbName, 'm$ClassX'      ,'Ileum'    ,'Colon'        ,
                                                                        'Sigmoid'       ,'Lumen'    ,
                                                                        'm$Class1'      ,''         ,
                                                                        ))
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_entire              ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,        
                                 run                = self.mainrun              ,Class_X    = None                  ,Class_1    = None                  ,
                                 Length             = len(d_entire)             ,)
        self.keyRank.append(fullname)
        #Get Endo_Ileum with
        #Class_X = Ileum and Class_1 != ''
        xname                  = 'Ileum'
        d_ileum                = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s!="%s")
                                                 """ % (self.rdpDbName, 'm$ClassX'  ,'Ileum'    ,'m$Class1'     ,''     ,
                                                        ))
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_ileum               ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 run                = self.mainrun              ,Class_X    = 'Ileum'               ,Class_1     = None                 ,
                                 Length             = len(d_ileum)              ,)
        self.keyRank.append(fullname)
        #Get Endo_Colon2 (Ascending_Colon + Transverse_Colon) with Class_1 != ''
        xname                  = 'Colon'
        d_colon                = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s!="%s")
                                                 """ % (self.rdpDbName, 'm$ClassX'     ,'Colon'         ,
                                                                        'm$Class1'     ,''              ,
                                                                        ))
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_colon               ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 run                = self.mainrun              ,Class_X    = None                  ,Class_1    = None                  ,
                                 Length             = len(d_colon)              ,)                                          
        self.keyRank.append(fullname)
        #Get Endo_Sigmoid with Class_2 and Class_3 has having values != ""
        xname                  = 'Sigmoid'
        d_sigmoid              = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s!="%s")
                                                 """ % (self.rdpDbName, 'm$ClassX'      ,'Sigmoid'      ,'m$Class1'     ,''             ,))
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_sigmoid             ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 run                = self.mainrun              ,Class_X    = None                  ,Class_1    = None                  ,
                                 Length             = len(d_sigmoid)            ,)                                          
        self.keyRank.append(fullname)
        #Get Endo_Lumen
        xname                  = 'Lumen'
        d_lumen                = self.t.query("""SELECT * FROM %s WHERE %s="%s"
                                                 """ % (self.rdpDbName,    'm$ClassX'  ,'Lumen'     ,))
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_lumen               ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 run                = self.mainrun              ,Class_X    = 'Lumen'               ,Class_1    = None                  ,
                                 Length             = len(d_lumen)              ,)                                              
        self.keyRank.append(fullname)
        #Get mucosa vs lumen of Ileum of C,UC,HC
        xname                  = 'Ileum_Lumen'
        d_ileum_lumen          = self.t.query("""SELECT * FROM %s WHERE ((%s in ("%s") and %s!="%s") or (%s in ("%s"))) and   
                                                                         (%s in ("%s","%s","%s","%s","%s","%s")) 
                                                                             
                                                 """ % (self.rdpDbNameM,    'm$ClassX'      ,'Ileum'    ,'m$Class1'     ,''                         ,
                                                                            'm$ClassX'      ,'Lumen'    ,
                                                                            'c$Disease'     ,
                                                                            'Crohn_Ileum'   ,'Ulcerative Colitis_Ileum' ,'Healthy Control_Ileum'    ,
                                                                            'Crohn_Lumen'   ,'Ulcerative Colitis_Lumen' ,'Healthy Control_Lumen'    ,))                                                  
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data   = d_ileum_lumen             ,dataoption  = Datap.Option.pair    ,
                                 keeplist           = ['Crohn_Ileum'               ,'Crohn_Lumen'                   ,
                                                       'Ulcerative Colitis_Ileum'  ,'Ulcerative Colitis_Lumen'      ,
                                                       'Healthy Control_Ileum'     ,'Healthy Control_Lumen'         ,
                                                       ],
                                 pair               = [('Crohn_Ileum','Crohn_Lumen')                                ,
                                                       ('Ulcerative Colitis_Ileum','Ulcerative Colitis_Lumen')      ,
                                                       ('Healthy Control_Ileum','Healthy Control_Lumen')            ,
                                                       ],                                                                                                                                                   
                                 run                = self.mainrun              ,
                                 Length             = len(d_ileum_lumen)        ,)                                              
        self.keyRank.append(fullname)
        #Get mucosa vs lumen of Colon of C,UC,HC
        xname                  = 'Colon_Lumen'
        d_colon_lumen          = self.t.query("""SELECT * FROM %s WHERE ( ((%s in ("%s") and %s!="%s") or (%s in ("%s"))) and   
                                                                            (%s in ("%s","%s","%s","%s","%s","%s")) 
                                                                            )
                                                 """ % (self.rdpDbNameM,    'm$ClassX'      ,'Colon'    ,'m$Class1'         ,''                         ,
                                                                            'm$ClassX'      ,'Lumen'    ,
                                                                            'c$Disease'     ,
                                                                            'Crohn_Colon'   ,'Ulcerative Colitis_Colon'     ,'Healthy Control_Colon'    ,
                                                                            'Crohn_Lumen'   ,'Ulcerative Colitis_Lumen'     ,'Healthy Control_Lumen'    ,))                                                   
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data   = d_colon_lumen             ,dataoption  = Datap.Option.pair    ,                                                     
                                 keeplist           = ['Crohn_Colon'                ,'Crohn_Lumen'                  ,
                                                       'Ulcerative Colitis_Colon'   ,'Ulcerative Colitis_Lumen'     ,
                                                       'Healthy Control_Colon'      ,'Healthy Control_Lumen'        ,
                                                       ],
                                 pair               = [('Crohn_Colon','Crohn_Lumen')                                ,
                                                       ('Ulcerative Colitis_Colon','Ulcerative Colitis_Lumen')      ,
                                                       ('Healthy Control_Colon','Healthy Control_Lumen')            ,
                                                       ],                                                                                                                                                   
                                 run                = self.mainrun              ,
                                 Length             = len(d_colon_lumen)        ,)
        self.keyRank.append(fullname)
        #Get mucosa vs lumen of Sigmoid of C,UC,HC
        xname                  = 'Sigmoid_Lumen'
        d_sigmoid_lumen        = self.t.query("""SELECT * FROM %s WHERE ( ((%s in ("%s") and %s!="%s") or (%s in ("%s"))) and   
                                                                              (%s in ("%s","%s","%s","%s","%s","%s")) 
                                                                            ) 
                                                 """ % (self.rdpDbNameM,    'm$ClassX'      ,'Sigmoid'  ,'m$Class1'         ,''                         ,
                                                                            'm$ClassX'      ,'Lumen'    ,
                                                                            'c$Disease'     ,
                                                                            'Crohn_Sigmoid' ,'Ulcerative Colitis_Sigmoid'  ,'Healthy Control_Sigmoid'  ,
                                                                            'Crohn_Lumen'   ,'Ulcerative Colitis_Lumen'    ,'Healthy Control_Lumen'    ,))
        fullname               = '%s_%s' % (self.rdpDbName,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                      ,data   = d_sigmoid_lumen       ,dataoption  = Datap.Option.pair    ,
                                 keeplist           = ['Crohn_Sigmoid'              ,'Crohn_Lumen'                  ,
                                                       'Ulcerative Colitis_Sigmoid' ,'Ulcerative Colitis_Lumen'     ,
                                                       'Healthy Control_Sigmoid'    ,'Healthy Control_Lumen'        ,
                                                       ],
                                 pair               = [('Crohn_Sigmoid','Crohn_Lumen')                             ,
                                                       ('Ulcerative Colitis_Sigmoid','Ulcerative Colitis_Lumen')   ,
                                                       ('Healthy Control_Sigmoid','Healthy Control_Lumen')         ,
                                                       ],                                                                                                                                                   
                                 run                = self.mainrun              ,
                                 Length             = len(d_sigmoid_lumen)      ,)
        self.keyRank.append(fullname)
        #Record Data
        #self.maindbname -> 'gutmtps'
        #self.rdpDbName    -> 'rdp8level1'
        for i in self.keyRank:
            #i -> 'Lumen'
            fullname                    = i
            self.rawdata[i].data        = utility.DatasetTools.finalBrush(self.rawdata[i].data)
            try:
                self.t.write(fullname,self.rawdata[i].data,overwrite = True)
            except (MySQLdb.OperationalError,):
                pass
            info_obj    = INFO(fullname         = self.rawdata[i].fullname          ,
                               dataoption       = self.rawdata[i].dataoption        ,
                               permremovelist   = self.rawdata[i].permremovelist    ,
                               keeplist         = self.rawdata[i].keeplist          ,
                               fixedcls         = self.rawdata[i].fixedcls          ,
                               matrix           = self.rawdata[i].matrix            ,
                               pair             = self.rawdata[i].pair              ,
                               run              = self.rawdata[i].run               ,
                               Class_X          = self.rawdata[i].Class_X           ,
                               Class_1          = self.rawdata[i].Class_1           ,                               
                               Length           = self.rawdata[i].Length            ,
                               )
            info_string = MySQLdb.escape_string(cPickle.dumps(info_obj,2))
            #Determine to insert new or update the table
            self.RD.query("""select %s from %s where %s="%s"
                          """ % ( self.RD.tab_info.name,self.RD.tab_info.tablename,self.RD.tab_info.name,fullname))
            row         = self.RD.cursor.fetchone()
            if row not in (None,()):
                #if full name does exist,update
                self.RD.query("""update %s set %s="%s" where %s="%s"
                              """ % ( self.RD.tab_info.tablename,self.RD.tab_info.info_obj,info_string,
                                                                 self.RD.tab_info.name,fullname ) )
            else:
                self.RD.query("""insert into %s (%s,%s) values  ("%s","%s")
                              """ % ( self.RD.tab_info.tablename,self.RD.tab_info.name,self.RD.tab_info.info_obj,
                                                                 fullname,info_string ) )
                

class GutMTPSDataPackage(DataPrePackage):
    
    def __init__(self,**kwargs):
        #Arbitrary properties
        self.Class_X        = None
        self.Class_1        = None
        self.__dict__.update(kwargs)
        DataPrePackage.__init__(self,**kwargs)
        
        
#class MTPSSettings(object):
#    
#    def __init__(self):
#        self.tableList      = None
        
        
class INFO(object):
    
    def __init__(self,**kwargs):
        self.fullname       = ''
        self.dataoption     = Datap.Option.fix
        self.permremovelist = ['']
        self.keeplist       = []
        self.fixedcls       = ''
        self.matrix         = [ [], [] ]
        self.pair           = []
        self.run            = None
        #Arbitrary properties
        self.Class_X        = None
        self.Class_1        = None
        self.Length         = None
        self.__dict__.update(kwargs)
        
        